{
 "cells": [
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# 拆分与合并",
   "id": "2962b3a2a5a26ca1"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 1. 合并",
   "id": "96e5e8478e1391df"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T08:27:24.342356Z",
     "start_time": "2025-09-15T08:27:23.891532Z"
    }
   },
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "path = 'D:/2506A/monty03/day16/file/'"
   ],
   "id": "a9bdd215eaaf50e",
   "outputs": [],
   "execution_count": 1
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T08:35:33.607771Z",
     "start_time": "2025-09-15T08:35:33.592881Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 演示 同一个工作簿中多个Sheet合并\n",
    "df = pd.read_excel(path + '合并.xlsx',None)\n",
    "\n",
    "new_data = pd.DataFrame() # 合并表\n",
    "sheets = list(df.keys()) # sheet\n",
    "for sheet in sheets:\n",
    "    # print(df[sheet])\n",
    "    new_data = pd.concat([new_data,df[sheet]])\n",
    "print(new_data)\n"
   ],
   "id": "394aa2b96ca03aa7",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   序号 姓名   电话\n",
      "0   1  A  123\n",
      "1   2  B  456\n",
      "2   3  C  789\n",
      "0   1  D  678\n",
      "1   2  E  901\n",
      "2   3  F  991\n",
      "0   1  X  555\n",
      "1   2  Y  666\n",
      "2   3  Z  777\n"
     ]
    }
   ],
   "execution_count": 13
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T08:44:30.726965Z",
     "start_time": "2025-09-15T08:44:30.708198Z"
    }
   },
   "cell_type": "code",
   "source": [
    "from pathlib import Path\n",
    "\n",
    "# 同一目录下多文件合并成一个文件\n",
    "path2 = path + '合并'\n",
    "\n",
    "new_df = pd.DataFrame()\n",
    "for file in Path(path2).iterdir():\n",
    "    df = pd.read_excel(file)\n",
    "    new_df = pd.concat([new_df,df])\n",
    "\n",
    "print(new_df)\n"
   ],
   "id": "9cbe66ab3b11a40",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   序号   姓名 性别\n",
      "0   1  谭鑫宇  女\n",
      "1   2  捏如风  女\n",
      "2   3  韩耀祖  男\n",
      "0   1   哪吒  男\n",
      "1   2   杨戬  男\n",
      "2   3  孙悟空  男\n",
      "0   1  林黛玉  女\n",
      "1   2  薛宝钗  女\n",
      "2   3  贾探春  女\n"
     ]
    }
   ],
   "execution_count": 20
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## ExcelWriter针对不同工作表的操作",
   "id": "8a7c82947ad97ece"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T08:49:12.235213Z",
     "start_time": "2025-09-15T08:49:12.120292Z"
    }
   },
   "cell_type": "code",
   "source": [
    "writer = pd.ExcelWriter(path + '12.xlsx')\n",
    "# 创建一个df\n",
    "df = pd.DataFrame({\n",
    "    '姓名':['韩耀祖','谭鑫宇','聂茹凤'],\n",
    "    '性别':['男','女','女']\n",
    "})\n",
    "\n",
    "df2 = pd.DataFrame({\n",
    "    '姓名':['杨过','小龙女','李莫愁'],\n",
    "    '性别':['男','女','女']\n",
    "})\n",
    "\n",
    "df.to_excel(writer,sheet_name='Sheet1')\n",
    "df2.to_excel(writer,sheet_name='Sheet2')\n",
    "\n",
    "writer.close()"
   ],
   "id": "4eb096e769554633",
   "outputs": [],
   "execution_count": 21
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# 2. 拆分",
   "id": "956e3a63b0576393"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T08:57:24.547895Z",
     "start_time": "2025-09-15T08:57:24.507199Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 将一个工作表拆分成多个sheet\n",
    "df = pd.read_excel(path + '拆分.xlsx')\n",
    "splits = list(df['部门'].drop_duplicates())  # 拆分列，去重\n",
    "print(splits)\n",
    "new_df = pd.ExcelWriter(path + '多个Sheet.xlsx')\n",
    "for i in splits:\n",
    "    df1 = df[df['部门'] == i ]\n",
    "    df1.to_excel(new_df,sheet_name=i)\n",
    "\n",
    "new_df.close()"
   ],
   "id": "aa288451b78c0fb8",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['办公室', '销售部', '保洁部']\n"
     ]
    }
   ],
   "execution_count": 26
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T09:01:29.555290Z",
     "start_time": "2025-09-15T09:01:29.459043Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 将一个工作表拆分成多个工作表\n",
    "df = pd.read_excel(path + '拆分.xlsx')\n",
    "splits = list(df['部门'].drop_duplicates())  # 拆分列，去重\n",
    "print(splits)\n",
    "for i in splits:\n",
    "    df1 = df[df['部门'] == i ]\n",
    "    df1.to_excel(f'{path}拆分/{i}.xlsx')\n",
    "\n",
    "new_df.close()"
   ],
   "id": "84d63246621d935c",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['办公室', '销售部', '保洁部']\n"
     ]
    }
   ],
   "execution_count": 29
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
